﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Collections.Generic;
using System.Data.Sql;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace Perm.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class LoadRoads
    {
        bool flag = false;
        string  selectMSSQL = "", String = "", 
               name = "", tsystem = "", 
               contractor = "", 
               error = null;
        double startX, startY,
               endX, endY,
               X, Y;
        int id, reit = 0, i = 0;

        SqlConnection conMSSQL;
        SqlDataReader readerMSSQL;
        SqlCommand commandMSSQL;

        DateTime thisDate = DateTime.Now.ToUniversalTime().AddHours(6);

        [OperationContract]
        public List<Road> DoWork(string city)
        {
            string connectionStringMSSQL = WebConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString;

            try {
                conMSSQL = new SqlConnection(connectionStringMSSQL);
                conMSSQL.Open();
            } catch { error += " Невозможно подключиться к базе данных. MS SQL (LoadRoads)"; }

                    switch (city)
                    {
                        case "perm":
                            {
                                //selectMSSQL = "SELECT ROAD_EKB.[NO], ROAD_EKB.NAME, RATING_ROAD_EKB.RATING, ROAD_EKB.[INDEX], ROAD_EKB.XCOORD, ROAD_EKB.YCOORD, ROAD_EKB.[XCOORD1], ROAD_EKB.[YCOORD1], ROAD_EKB.[XCOORD2], ROAD_EKB.[YCOORD2], ROAD_EKB.[TSYSSET] FROM ROAD_EKB INNER JOIN RATING_ROAD_EKB ON ROAD_EKB.[NO] = RATING_ROAD_EKB.ID ORDER BY ROAD_EKB.[NO], ROAD_EKB.[INDEX]";
                                selectMSSQL = "SELECT ROAD_PERM.[NO], ROAD_PERM.NAME, RATING_ROAD_PERM.RATING, ROAD_PERM.[INDEX], ROAD_PERM.XCOORD, ROAD_PERM.YCOORD, ROAD_PERM.[XCOORD1], ROAD_PERM.[YCOORD1], ROAD_PERM.[XCOORD2], ROAD_PERM.[YCOORD2], ROAD_PERM.[TSYSSET], ROAD_PERM.PODRYADCHIK FROM ROAD_PERM INNER JOIN RATING_ROAD_PERM ON ROAD_PERM.[NO] = RATING_ROAD_PERM.ID ORDER BY ROAD_PERM.[NO], ROAD_PERM.[INDEX]";
                                //select = "SELECT LINK.[NO], LINK.NAME, LINK.RATING, LINKPOLY.INDEX, LINKPOLY.XCOORD, LINKPOLY.YCOORD, First(NODE.XCOORD) AS [First-XCOORD], First(NODE.YCOORD) AS [First-YCOORD], First(NODE_1.XCOORD) AS [First-XCOORD1], First(NODE_1.YCOORD) AS [First-YCOORD1], Max(LINK.TSYSSET) AS [Max-TSYSSET], LINK.PODRYADCHIK FROM NODE AS NODE_1 INNER JOIN (NODE INNER JOIN (LINK LEFT JOIN LINKPOLY ON (LINK.FROMNODENO = LINKPOLY.FROMNODENO) AND (LINK.TONODENO = LINKPOLY.TONODENO)) ON NODE.[NO] = LINK.FROMNODENO) ON NODE_1.[NO] = LINK.TONODENO GROUP BY LINK.[NO], LINK.NAME, LINK.RATING, LINKPOLY.INDEX, LINKPOLY.XCOORD, LINKPOLY.YCOORD, LINK.PODRYADCHIK HAVING (((Max(LINK.TSYSSET)) Not Like '%GD%')) ORDER BY LINK.[NO], LINKPOLY.INDEX";
                                //только главные дороги select = "SELECT LINK.[NO], LINK.NAME, LINK.RATING, LINKPOLY.INDEX, LINKPOLY.XCOORD, LINKPOLY.YCOORD, First(NODE.XCOORD) AS [First-XCOORD], First(NODE.YCOORD) AS [First-YCOORD], First(NODE_1.XCOORD) AS [First-XCOORD1], First(NODE_1.YCOORD) AS [First-YCOORD1], Max(LINK.TSYSSET) AS [Max-TSYSSET], LINK.PODRYADCHIK, First(LINK.ZOOM) AS [First-ZOOM] FROM NODE AS NODE_1 INNER JOIN (NODE INNER JOIN (LINK LEFT JOIN LINKPOLY ON (LINK.FROMNODENO = LINKPOLY.FROMNODENO) AND (LINK.TONODENO = LINKPOLY.TONODENO)) ON NODE.[NO] = LINK.FROMNODENO) ON NODE_1.[NO] = LINK.TONODENO GROUP BY LINK.[NO], LINK.NAME, LINK.RATING, LINKPOLY.INDEX, LINKPOLY.XCOORD, LINKPOLY.YCOORD, LINK.PODRYADCHIK HAVING (((Max(LINK.TSYSSET)) Not Like '%GD%') AND ((First(LINK.ZOOM))=2)) ORDER BY LINK.[NO], LINKPOLY.INDEX";                                                           
                                break;
                            }
                        case "ekb":
                            {
                                selectMSSQL = "SELECT ROAD_EKB.[NO], ROAD_EKB.NAME, RATING_ROAD_EKB.RATING, ROAD_EKB.[INDEX], ROAD_EKB.XCOORD, ROAD_EKB.YCOORD, ROAD_EKB.[XCOORD1], ROAD_EKB.[YCOORD1], ROAD_EKB.[XCOORD2], ROAD_EKB.[YCOORD2], ROAD_EKB.[TSYSSET], ROAD_EKB.PODRYADCHIK FROM ROAD_EKB INNER JOIN RATING_ROAD_EKB ON ROAD_EKB.[NO] = RATING_ROAD_EKB.ID ORDER BY ROAD_EKB.[NO], ROAD_EKB.[INDEX]";
                                break;
                            }
                        case "permkray":
                            {
                                selectMSSQL = "SELECT ROAD_PERMKRAY.[NO], ROAD_PERMKRAY.NAME, RATING_ROAD_PERMKRAY.RATING, ROAD_PERMKRAY.[INDEX], ROAD_PERMKRAY.XCOORD, ROAD_PERMKRAY.YCOORD, ROAD_PERMKRAY.XCOORD1, ROAD_PERMKRAY.YCOORD1, ROAD_PERMKRAY.XCOORD2, ROAD_PERMKRAY.YCOORD2, ROAD_PERMKRAY.TSYSSET, ROAD_PERMKRAY.PODRYADCHIK FROM RATING_ROAD_PERMKRAY INNER JOIN ROAD_PERMKRAY ON RATING_ROAD_PERMKRAY.ID = ROAD_PERMKRAY.[NO] ORDER BY ROAD_PERMKRAY.[NO], ROAD_PERMKRAY.[INDEX]"; 
                                break;
                            }
                    }                    
            try {
                commandMSSQL = new SqlCommand(selectMSSQL, conMSSQL);
                readerMSSQL = commandMSSQL.ExecuteReader();
            } catch { error += "Невозможно считать данный из базы. MSSQL. "; }
            List<Road> RoadsList = new List<Road>();          

            while (readerMSSQL.Read())
            {
                Road road = new Road();
                
                id = readerMSSQL.GetInt32(0);                

                if (readerMSSQL[3].ToString() == "")
                {
                    startX = Math.Round(readerMSSQL.GetDouble(8), 2);
                    startY = Math.Round(readerMSSQL.GetDouble(9), 2);
                    endX = Math.Round(readerMSSQL.GetDouble(6), 2);
                    endY = Math.Round(readerMSSQL.GetDouble(7), 2);
                }
                
                if (readerMSSQL[3].ToString() != "")
                {
                    if (readerMSSQL.GetInt32(0) == id)
                    {
                        road.X += startX + ";";
                        road.Y += startY + ";";

                        while (readerMSSQL.GetInt32(0) == id)
                        {
                            int index = readerMSSQL.GetInt32(3);
                            X = Math.Round(readerMSSQL.GetDouble(4), 2);
                            Y = Math.Round(readerMSSQL.GetDouble(5), 2);
                            road.X += X + ";";
                            road.Y += Y + ";";
                            readerMSSQL.Read(); 
                        }
                        road.X += endX;
                        road.Y += endY;
                        RoadsList[i - 1].X = road.X;
                        RoadsList[i - 1].Y = road.Y;
                        if (readerMSSQL.GetInt32(0) != id)
                        {
                            road = new Road();
                            id = readerMSSQL.GetInt32(0);
                            startX = Math.Round(readerMSSQL.GetDouble(8), 2);
                            startY = Math.Round(readerMSSQL.GetDouble(9), 2);
                            endX = Math.Round(readerMSSQL.GetDouble(6), 2);
                            endY = Math.Round(readerMSSQL.GetDouble(7), 2);
                            road.X += startX + ";";
                            road.Y += startY + ";";
                            road.X += endX;
                            road.Y += endY;

                            name = readerMSSQL[1].ToString();
                            tsystem = readerMSSQL[10].ToString();
                            contractor = (readerMSSQL[11].ToString() != "")? readerMSSQL[11].ToString() : "неизвестно"; 
                            reit = (readerMSSQL[2].ToString() != "") ? (int)readerMSSQL.GetDouble(2) : 0;

                            road.id = id;
                            road.reit = reit;
                            road.contractor = contractor;
                            road.tsystem = tsystem;
                            road.name = name;
                            RoadsList.Add(road);               
                        }
                    }
                }
                else
                {                    
                    road.X += startX + ";";
                    road.Y += startY + ";";
                    road.X += endX;
                    road.Y += endY;

                    name = readerMSSQL[1].ToString();
                    tsystem = readerMSSQL[10].ToString();
                    try { contractor = readerMSSQL[11].ToString(); } catch { contractor = "неизвестно" ;}
                    reit = (readerMSSQL[2].ToString() != "") ? (int)readerMSSQL.GetDouble(2) : 0;

                    road.id = id;
                    road.reit = reit;
                    road.contractor = contractor;
                    road.tsystem = tsystem;
                    road.name = name;
                    RoadsList.Add(road);
                }
                
                i++;
            }
            if (error != null)
            {
                switch (city)
                {
                    case "perm":
                        {
                            String = "INSERT INTO ERRORS (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                            break;
                        }
                    case "ekb":
                        {
                            String = "INSERT INTO ERRORS_EKB (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                            break;
                        }
                    case "permkray":
                        {
                            String = "INSERT INTO ERRORS_PERMKRAY (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                            break;
                        }
                }
                try
                {
                    commandMSSQL = new SqlCommand(String, conMSSQL);
                    commandMSSQL.ExecuteNonQuery();
                }
                catch { }
            }
            conMSSQL.Close();
            return RoadsList;
        }
        [DataContract]
        public class Road
        {
            [DataMember]
            public int id = 0;
            [DataMember]
            public string name = "";
            [DataMember]
            public string tsystem = "";
            [DataMember]
            public int reit = 0;
            [DataMember]
            public string X = "";
            [DataMember]
            public string Y = "";
            [DataMember]
            public string contractor = "";
        }
    }
}
